package forever.com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import forever.com.dao.intf.LandMarkDAOIntf;
import forever.com.util.JDBCUtil;
import forever.com.vo.LandMarkVO;
import forever.com.vo.PhotoVO;
import forever.com.vo.VideoVO;

public class LandMarkDAOImpl implements LandMarkDAOIntf {

	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String sql = "";

	
	public boolean addLandMark(LandMarkVO landmark) {
		int result = 0;
		sql = "insert into landmark(tripmemory_id,landmark_name,landmark_date,landmark_content) values(?,?,?,?)";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, landmark.getTripmemory_id());
			ps.setString(2, landmark.getLandmark_name());
			ps.setTimestamp(3, new java.sql.Timestamp(landmark.getLandmark_date().getTime()));
			ps.setString(4, landmark.getLandmark_content());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result == 1;
	}

	public boolean addPhoto(PhotoVO photo) {
		int result = 0;
		sql = "insert into photo(landmark_id,photo_url,photo_description) values(?,?,?)";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, photo.getLandmark_id());
			ps.setString(2, photo.getPhoto_url());
			ps.setString(3, photo.getPhoto_description());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result == 1;
	}

	public boolean addVideo(VideoVO video) {
		int result = 0;
		sql = "insert into video(landmark_id,video_url,video_description) values(?,?,?)";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, video.getLandmark_id());
			ps.setString(2, video.getVideo_url());
			ps.setString(3, video.getVideo_description());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result == 1;
	}

	public boolean deleteLandMark(int landmarkid) {
		int result = 0;
		sql = "delete from landmark where landmark_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, landmarkid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result==1;
	}

	public boolean deletePhoto(int photoid) {
		int result = 0;
		sql = "delete from photo where photo_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, photoid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result==1;
	}

	public boolean deleteVideo(int videoid) {
		int result = 0;
		sql = "delete from video where video_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, videoid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result==1;
	}

	public boolean modifyLandMark(LandMarkVO landmark) {
		int result = 0;
		sql = "update landmark set landmark_name = ?, landmark_date = ?, landmark_content = ? where landmark_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, landmark.getLandmark_name());
			ps.setTimestamp(2, new Timestamp(landmark.getLandmark_date().getTime()));
			ps.setString(3, landmark.getLandmark_content());
			ps.setInt(4, landmark.getLandmark_id());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result==1;
	}

	//By WangDc
	public List<LandMarkVO> getLandmarkList(int tripmemoryid){
		List<LandMarkVO> list = new ArrayList<LandMarkVO>();
		LandMarkVO landmark = null;
		sql = "select * from landmark where tripmemory_id = ? order by landmark_id desc";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, tripmemoryid);
			rs = ps.executeQuery();
			while(rs.next()){
				landmark = new LandMarkVO();
				
				landmark.setLandmark_id(rs.getInt("landmark_id"));
				landmark.setLandmark_name(rs.getString("landmark_name"));
				landmark.setTripmemory_id(rs.getInt("tripmemory_id"));
				landmark.setLandmark_date(new java.util.Date((rs.getDate("landmark_date").getTime())));
				landmark.setLandmark_content(rs.getString("landmark_content"));
				
				list.add(landmark);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}if(list.isEmpty()){
			return null;
		}else{
			return list;
		}
	}
	
	//By WangDc
	public List<PhotoVO> getPhotoList(int landmarkid){
		List<PhotoVO> list = new ArrayList<PhotoVO>();
		PhotoVO photo = null;
		sql = "select * from photo where landmark_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, landmarkid);
			rs = ps.executeQuery();
			while(rs.next()){
				photo = new PhotoVO();
				
				photo.setPhoto_id(rs.getInt("photo_id"));
				photo.setLandmark_id(rs.getInt("landmark_id"));
				photo.setPhoto_url(rs.getString("photo_url"));
				photo.setPhoto_description(rs.getString("photo_description"));
				
				list.add(photo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}if(list.isEmpty()){
			return null;
		}else{
			return list;
		}
	}
	
	//By WangDc
	public List<VideoVO> getVideoList(int landmarkid){
		List<VideoVO> list = new ArrayList<VideoVO>();
		VideoVO video = null;
		sql = "select * from video where landmark_id = ?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, landmarkid);
			rs = ps.executeQuery();
			while(rs.next()){
				video = new VideoVO();
				
				video.setVideo_id(rs.getInt("video_id"));
				video.setLandmark_id(rs.getInt("landmark_id"));
				video.setVideo_url(rs.getString("video_url"));
				video.setVideo_description(rs.getString("video_description"));
				
				list.add(video);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				JDBCUtil.Close(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}if(list.isEmpty()){
			return null;
		}else{
			return list;
		}
	}
}
